package com.yc.chaoshi.cs_order.mapper;

import com.yc.chaoshi.entity.PmsGoods;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

/**
 * <p>
 *  Mapper 接口
 * </p>
 *
 * @author xz
 * @since 2022-08-04
 */
public interface PmsGoodsMapper extends BaseMapper<PmsGoods> {

    @Select("select * from pms_goods a join pms_fenlei3 b on a.oid=b.cid " +
            " join pms_fenlei2 c on b.bid=c.bid  " +
            " where c.aid=#{aid}  " +
            " or b.bid= #{aid}" +
            " or b.cid= #{aid}" +
            " limit #{page},#{size}")
    List<PmsGoods> selectByAid(
            @Param("aid") int aid,
            @Param("page") int page,
            @Param("size") int size);

    @Select("select count(*) cnt from pms_goods a join pms_fenlei3 b on a.oid=b.cid  " +
            " join pms_fenlei2 c on b.bid=c.bid " +
            " where c.aid=#{aid} " +
            " or b.bid= #{aid}" +
            " or b.cid= #{aid}" )
    Map<String,Object> countByAid(@Param("aid")int aid);


    @Select("select * from pms_goods a join pms_fenlei3 b on a.oid=b.cid " +
            " where b.bid=#{aid}" +
            " limit #{page},#{size}")
    List<PmsGoods> selectByBid(
            @Param("aid") int aid,
            @Param("page") int page,
            @Param("size") int size);

    @Select("select count(*) cnt from pms_goods a join pms_fenlei3 b on a.oid=b.cid " +
            "  where b.bid=#{aid} ")
    Map<String,Object> countByBid(@Param("aid")int aid);

    @Select("select * from pms_goods where oid=#{cid}" +
            " limit #{page},#{size}")
    List<PmsGoods> selectByCid(
            @Param("cid") int cid,
            @Param("page") int page,
            @Param("size") int size);

    @Select("select count(*) cnt from pms_goods  where oid=#{cid}" )
    Map<String,Object> countByCid(@Param("cid")int cid);

    // 查询有折扣的随机五个商品
    @Select("select * from pms_goods where discount_price!=0 order by rand() limit 5")
    List<PmsGoods> findDiscount();

    // 随机查询 12组 销量前一百的商品
    @Select("select * from (select * from pms_goods ORDER BY sales DESC limit 100) a order by rand() limit 12")
    List<PmsGoods> findHot();

    // 查询销量最好的五个商品
    @Select("select * from pms_goods ORDER BY sales DESC limit 5")
    List<PmsGoods> findMaxHot();

    // 查询销量最好的四个3级品类
    @Select("select * from (select cid,cname,SUM(sales) sales from pms_goods a join pms_fenlei3 b on a.oid=b.cid group by oid)a ORDER BY sales DESC limit 4")
    List<PmsGoods> findHotF3();

    @Select("select * from pms_goods where name like CONCAT( '%',#{name},'%') ORDER BY sales desc" +
            " limit #{page},#{size}" )
    List<PmsGoods> queryGoods(@Param("name") String name,
                              @Param("page") int page,
                              @Param("size") int size);
    @Select("select count(*) cnt from pms_goods  where name like CONCAT( '%',#{name},'%') ORDER BY sales desc" )
    Map<String,Object> countGoods(@Param("name")String name);

}
